﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace Sbk_Manage
{
    public partial class Frm_DeviceManager : Form
    {
        public Frm_DeviceManager()
        {
            InitializeComponent();
        }

        private void BtnReset_Click(object sender, EventArgs e)
        {
            TxtHtBh.Text = "";
            this.TxtSbMc.Text = "";
            this.TxtCcbh.Text = "";
            this.Txtghs.Text = "";
            this.TxtSbBarcode.Text = "";
            this.TxtSbBfpzwh.Text = "";
            this.TxtSbCj.Text = "";
            this.TxtSbsyks.Text = "";
            this.dtpSbysrq.Checked = false;
            this.DtPSbRzrq.Checked = false;
            ToolStripStatusLabel1.Text = "";
        }

        private void AddContractBtn_Click(object sender, EventArgs e)
        {
            Frm_AddDevice ins = new Frm_AddDevice();
            if (ins.ShowDialog() == DialogResult.OK)
            {
                BtnReset.PerformClick();
                BtnQuery.PerformClick();
            }
        }

        private void EditContractBtn_Click(object sender, EventArgs e)
        {
            if (DgvSbInfo.RowCount > 0 && DgvSbInfo.SelectedRows.Count == 1)
            {
                //主键
                Int32 CursbID = Convert.ToInt32(DgvSbInfo.SelectedRows[0].Cells["id"].Value);
                FrmAuth ins = new FrmAuth();
                if (ins.ShowDialog() == DialogResult.OK)
                {
                    Frm_EditDevice insHt = new Frm_EditDevice();
                    insHt.CursbID = CursbID;
                    if (insHt.ShowDialog() == DialogResult.OK)
                    {
                        BtnQuery.PerformClick();
                    }
                }
            }
        }

        private void DelContractBtn_Click(object sender, EventArgs e)
        {
            if (DgvSbInfo.RowCount > 0 && DgvSbInfo.SelectedRows.Count == 1)
            {
                //主键
                Int32 CursbID = Convert.ToInt32(DgvSbInfo.SelectedRows[0].Cells["id"].Value);
                FrmAuth ins = new FrmAuth();
                if (ins.ShowDialog() == DialogResult.OK)
                {
                    if (MessageBox.Show("确定要删除当前选定的设备麽？", "麦禾合同管理系统", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                    {
                        string sqlstr = "delete from sb_info where id=" + CursbID;
                        if (Program.SqliteDB.ExecuteNonQuery(sqlstr) == 1)
                        {
                            MessageBox.Show("删除设备成功!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                            BtnQuery.PerformClick();
                        }
                    }
                }
            }
        }
        //
        private void DgvSbInfo_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            System.Drawing.Rectangle Rectangle = new System.Drawing.Rectangle(e.RowBounds.Location.X, e.RowBounds.Location.Y, DgvSbInfo.RowHeadersWidth - 4, e.RowBounds.Height);
            TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(), DgvSbInfo.RowHeadersDefaultCellStyle.Font, Rectangle, DgvSbInfo.RowHeadersDefaultCellStyle.ForeColor, TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
        }

        private void DgvSbInfo_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (DgvSbInfo.Columns[e.ColumnIndex].Name.Equals("bf_flag"))
            {
                if (e.Value.ToString().Equals("1"))
                {
                    e.Value = "已报废";
                    //已经借出
                    e.CellStyle.ForeColor = Color.Red;
                }
                else
                {
                    e.Value = "未报废";
                }
            }
            if (DgvSbInfo.Columns[e.ColumnIndex].Name.Equals("jk_flag"))
            {
                if (e.Value.ToString().Equals("1"))
                {
                    e.Value = "进口";
                    //已经扫描
                    e.CellStyle.ForeColor = Color.Green;
                }
                else
                {
                    e.Value = "非进口";
                }
            }
            if (DgvSbInfo.Columns[e.ColumnIndex].Name.Equals("tf_flag"))
            {
                if (e.Value.ToString().Equals("1"))
                {
                    e.Value = "投放";
                    //已经扫描
                    e.CellStyle.ForeColor = Color.Green;
                }
                else
                {
                    e.Value = "非投放";
                }
            }
        }

        private void Frm_DeviceManager_Load(object sender, EventArgs e)
        {
            //设置datagridview的列宽自动适应,列不可排序
            for (int i = 0; i < DgvSbInfo.ColumnCount; i++)
            {
                DgvSbInfo.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            }
            BtnQuery.PerformClick();
        }
        //默认每页显示记录数
        int offsetRows = 120;
        //当前页数
        int CurPage = 1;
        //总记录数
        int TotalRows = 0;
        //总页数
        int TotalPages = 0;
        //最后一页的记录数
        int LastPageRows = 0;
        string SqlPageStr = "select id,mc,xhgg,sbsn,barcode,bp,zczh,syks,ghsmc,ghslxfs,cjmc,cjlxfs,zbq,sbsl,danjia,memo_note,sblb,tf_flag,jk_flag,wmgsmc,bgd,jyjyzm,zybkzpjb,bf_flag,bfwjph,strftime('%Y-%m-%d',ysrq) as ysrq,strftime('%Y-%m-%d',rzrq) as rzrq,strftime('%Y-%m-%d',bfrq) as bfrq,strftime('%Y-%m-%d',xcjyrq) as xcjyrq,htbh from sb_info where 1=1  ";
        //定义查询字符串
        StringBuilder SqlQueryStr = new StringBuilder();
        private void BtnQuery_Click(object sender, EventArgs e)
        {
            BtnQuery.Focus();
            //每次查询都清空
            SqlQueryStr.Remove(0, SqlQueryStr.Length);
            //组合条件
            if (!TxtHtBh.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and htbh='{0}'", TxtHtBh.Text.Trim().Replace("'", ""));
            }
            if (!Txtghs.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and ghsmc like '%{0}%'", Txtghs.Text.Trim().Replace("'", ""));
            }
            if (!this.TxtSbMc.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and mc like '%{0}%'", TxtSbMc.Text.Trim().Replace("'", ""));
            }
            if (!this.TxtCcbh.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and sbsn = {0}", this.TxtCcbh.Text.Trim().Replace("'", ""));
            }
            if (!this.TxtSbBarcode.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and barcode = {0}", this.TxtSbBarcode.Text.Trim().Replace("'", ""));
            }
            if (!this.TxtSbsyks.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and  syks like '%{0}%'", TxtSbsyks.Text.Trim().Replace("'", ""));
            }
            if (!TxtSbCj.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and cjmc like '%{0}%'", TxtSbCj.Text.Trim().Replace("'", ""));
            }
            if (!TxtSbBfpzwh.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and bfwjph = {0}", TxtSbBfpzwh.Text.Trim().Replace("'", ""));
            }
            if (this.ChkSbysrq.Checked)
            {
                SqlQueryStr.AppendFormat(" and ysrq >= '{0}'", this.dtpSbysrq.Value.Date.ToString("s"));
            }
            if (this.Chkrzrq.Checked)
            {
                SqlQueryStr.AppendFormat(" and rzrq >= '{0}'", this.DtPSbRzrq.Value.Date.ToString("s"));
            }
            //初次查询
            InitLoad();
        }
        //初次查询
        private void InitLoad()
        {
            //分页查询，第一步是查询出总记录数；之后再分页
            string strSqlCount = "Select count(*) from sb_info where 1=1 " + SqlQueryStr.ToString();
            //总行数
            TotalRows = Convert.ToInt32(Program.SqliteDB.ExecuteScalar(strSqlCount));

            if (TotalRows > 0)
            {
                //总页数
                LastPageRows = TotalRows % offsetRows;
                if (LastPageRows == 0)
                {
                    TotalPages = TotalRows / offsetRows;
                }
                else
                {
                    TotalPages = TotalRows / offsetRows + 1;
                }
                //设置‘上一页’和‘下一页’按钮的可用性
                if (TotalPages > 1)
                {
                    BtnNext.Enabled = true;
                }
                else
                {
                    BtnNext.Enabled = false;
                }
                //第一次上一页不可用
                BtnPreview.Enabled = false;
                //当前页
                CurPage = 1;
                //显示限定第一页内容；形成查询字符串
                string sqlstr = SqlPageStr + SqlQueryStr.ToString() + " order by id desc,htbh desc LIMIT  " + offsetRows + " OFFSET  " + (CurPage - 1) * offsetRows;
                DgvSbInfo.DataSource = Program.SqliteDB.GetDataTable(sqlstr);
                if (DgvSbInfo.Rows.Count > 0)
                {
                    ToolStripStatusLabel1.Text = "共" + TotalPages + "页；当前第" + CurPage + "页；显示" + DgvSbInfo.Rows.Count.ToString() + "条设备信息";
                }
            }
            else
            {
                BtnPreview.Enabled = false;
                BtnNext.Enabled = false;
                //清空原有数据
                if (DgvSbInfo.Rows.Count > 0)
                {
                    for (int i = DgvSbInfo.Rows.Count - 1; i >= 0; i--)
                    {
                        DgvSbInfo.Rows.RemoveAt(i);
                    }
                }
                ToolStripStatusLabel1.Text = "不存在满足此条件的设备！请重置查询条件后查询！";
            }
        }

        private void DgvSbInfo_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            //选中先前的那条
            if (!OldSelectedId.Equals(""))
            {
                SelectedOldID(OldSelectedId);
            }
        }
        private void DivPageSelect(Boolean FX)
        {
            //判断设置按钮可用性 下一页
            if (FX == true)
            {
                CurPage += 1;
                if (CurPage == TotalPages)
                {
                    //已经到了最后一页，置'下一页'按钮不再可用
                    BtnNext.Enabled = false;
                    BtnPreview.Enabled = true;
                }
                else
                {
                    BtnPreview.Enabled = true;
                }
            }
            //上一页
            if (FX == false)
            {
                CurPage -= 1;
                if (CurPage == 1)
                {
                    //已经到了第一页，置'上一页'按钮不再可用
                    BtnPreview.Enabled = false;
                    BtnNext.Enabled = true;
                }
                else
                {
                    BtnNext.Enabled = true;
                }
            }
            //形成查询字符串
            string sqlstr = SqlPageStr + SqlQueryStr.ToString() + " order by id desc,htbh desc LIMIT  " + offsetRows + " OFFSET  " + (CurPage - 1) * offsetRows;
            DgvSbInfo.DataSource = Program.SqliteDB.GetDataTable(sqlstr);
            if (DgvSbInfo.Rows.Count > 0)
            {
                ToolStripStatusLabel1.Text = "共" + TotalPages + "页；当前第" + CurPage + "页；显示" + DgvSbInfo.Rows.Count.ToString() + "条设备信息";
            }
        }
        string OldSelectedId = "";
        //选中指定id的记录
        private void SelectedOldID(string id)
        {
            if (id.Equals(""))
            {
                return;
            }
            for (int i = 0; i < DgvSbInfo.Rows.Count; i++)
            {
                if (DgvSbInfo.Rows[i].Cells["id"].Value.ToString() == id)
                {
                    //先清空以前选中的所有行
                    DgvSbInfo.ClearSelection();
                    //最前面的黑色三角号跟着移动
                    DgvSbInfo.CurrentCell = DgvSbInfo.Rows[i].Cells["htbh"];
                    //滚动条跟着移动
                    DgvSbInfo.FirstDisplayedScrollingRowIndex = DgvSbInfo.Rows[i].Index;
                    //选中当前执行ris_no的行
                    DgvSbInfo.Rows[i].Selected = true;
                    OldSelectedId = id;
                    break;
                }
                else
                {
                    OldSelectedId = "";
                }
            }
        }

        private void BtnPreview_Click(object sender, EventArgs e)
        {
            DivPageSelect(false);
        }

        private void BtnNext_Click(object sender, EventArgs e)
        {
            DivPageSelect(true);
        }

        private void DgvSbInfo_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.RowIndex == -1)
            {
                return;
            }
            if (DgvSbInfo.RowCount > 0 && DgvSbInfo.SelectedRows.Count == 1)
            {
                //记录当前
                OldSelectedId = DgvSbInfo.SelectedRows[0].Cells["id"].Value.ToString();
            }
        }

        private void ExportExcelBtn_Click(object sender, EventArgs e)
        {

            try
            {
                DataTable dt = Program.SqliteDB.GetDataTable(SqlPageStr + " order by id asc,htbh asc ");
                if (dt != null && dt.Rows.Count > 0)
                {
                    //
                    SaveFileDialog sfd = new SaveFileDialog();
                    //设置文件类型 
                    sfd.Filter = "Excel文件（*.xls）|*.xls";
                    //设置默认文件类型显示顺序 
                    sfd.FilterIndex = 1;
                    //保存对话框是否记忆上次打开的目录 
                    sfd.RestoreDirectory = true;

                    //点了保存按钮进入 
                    if (sfd.ShowDialog() == DialogResult.OK)
                    {
                        string localFilePath = sfd.FileName.ToString(); //获得文件路径 
                        //存在先删除
                        if (File.Exists(localFilePath) == true)
                        {
                            File.Delete(localFilePath);
                        }
                        //创建空的
                        FileInfo fi = new FileInfo(localFilePath);
                        FileStream fs = fi.Create();
                        fs.Close();
                        fs.Dispose();
                        //
                        ExportExcelBtn.Enabled = false;
                        Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(localFilePath);
                        wk.Worksheets[0].Name = "设备信息列表";
                        AddExcelSHeet(wk, dt);
                        wk.Save(localFilePath);
                        MessageBox.Show("导出成功：" + localFilePath, "设备信息列表数据导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ExportExcelBtn.Enabled = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出错误：" + ex.ToString());
            }
        }
        private void AddExcelSHeet(Aspose.Cells.Workbook wk, DataTable dt)
        {

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    wk.Worksheets[0].Cells[i, k].PutValue(dt.Rows[i][k].ToString());
                }
            }
            wk.Worksheets[0].AutoFitColumns();
            wk.Worksheets[0].AutoFitRows();
        }
    }
}
